package cn.favebook.listener;

import java.sql.SQLException;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

import org.apache.commons.dbutils.QueryRunner;
import org.h2.jdbcx.JdbcDataSource;

import cn.favebook.Global;


@WebListener
public class DBStartListener implements ServletContextListener {
	
	@Override
	public void contextDestroyed(ServletContextEvent sce) {
		
	}

	@Override
	public void contextInitialized(ServletContextEvent sce) {
		String path = sce.getServletContext().getRealPath("/h2demo");
		System.out.println(path);
		JdbcDataSource jdbc = new JdbcDataSource();
		jdbc.setURL("jdbc:h2:" + path);
		
		String createUserTableSql = "create table if not exists user("
				+ "id int auto_increment primary key,"
				+ "username varchar(128) unique not null,"
				+ "email varchar(50) not null,"
				+ "password varchar(128) not null,"
				+ "sex char(1),"
				+ "headphoto varchar(128))";
					
		String createWeiboTableSql = "create table if not exists weibo("
				+ "id int auto_increment primary key,"
				+ "content text,"
				+ "publishDate datetime not null,"
				+ "userId int references user(id),"
				+ "userName varchar(128) references user(username))";
		
		String createReviewTableSql = "create table if not exists review("
				+ "id int auto_increment primary key,"
				+ "weiboId int references weibo(id),"
				+ "reviewerId int references user(id),"
				+ "content text,"
				+ "reviewDate datetime not null)";
		
		QueryRunner runner = new QueryRunner(jdbc);
		sce.getServletContext().setAttribute(Global.KEY_QUERY_RUNNER, runner);
		try {
			runner.execute(createUserTableSql);
			runner.execute(createWeiboTableSql);
			runner.execute(createReviewTableSql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	
}
